<?php
// Contiene metodos y funciones para gestionar la base de datos
include_once("DataBaseManager.php");

class FrontendManager extends DataBaseManager{

	// atributos heredados
	// $db
	
	// constructor
	function __construct(){
		$db_server = "ArquitectosDB.db.10377925.hostedresource.com";
		$db_user = "ArquitectosDB";	
		$db_pass = "Prueba123!";
		$db_database = "ArquitectosDB";
		parent::__construct($db_server, $db_user, $db_pass, $db_database);
	}		


	/*----------------------------------------ARQUITECTOS-----------------------------------------*/

	function getArticulos(){
		$sql="SELECT a.Titulo FROM articulos a WHERE a.Estado = 1;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}

	function getArticulosCategoria($idCategoria){
		$sql="SELECT a.Titulo FROM articulos a WHERE a.Categoria = ". $idCategoria ." AND a.Estado = 1;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}

	function getArticulo($idArticulo){
		$sql="SELECT a.* FROM articulos a WHERE a.id = ". $idArticulo ." AND a.Estado = 1;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}

	function getArticulosIndex(){
		$sql="SELECT a.id, a.Titulo, SUBSTRING( a.contenido, 1, 445 ) AS contenido, a.Imagen, c.Nombre
		FROM articulos a
		JOIN cat_categoria c ON ( c.id = a.Categoria ) 
		LIMIT 0 , 4";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}

	function getArticulosAdmin($idCategoria){
		$sql="SELECT a.id, a.Titulo, CASE Estado when 1 then 'Visible' when 2 then 'No Visible' END , a.Imagen,a.Estado AS e
		FROM articulos a
		WHERE Categoria=$idCategoria ORDER BY FechaHoraCrea DESC";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}

	function setEstadoArticulo($id,$Estado){
		$sql="UPDATE articulos set Estado=$Estado WHERE id=$id ";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}
		return $result;
	}




	/*----------------------------------------FIN ARQUITECTOS-----------------------------------------*/




























	/*----------------------------------------EMPRESAS-----------------------------------------*/
	
	function RecuperarEmpresas(){
		$sql="SELECT a.* FROM empresa a WHERE defaulte = FALSE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarEmpresaDefault(){
		$sql="SELECT a.* FROM empresa a WHERE defaulte = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarEmpresa($empresa){
		$sql="SELECT a.* FROM empresa a WHERE id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFEG(){
		$sql="SELECT a.* FROM fegallery a WHERE visible = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------DESTACADOS-----------------------------------------*/

	function RecuperarProductosDestacados($empresa){
		$sql="SELECT a.* FROM producto a WHERE id_empresa = $empresa AND a.destacado = TRUE AND a.habilitado = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarNoticiasDestacadas($empresa){
		$sql="SELECT a.* FROM publicacion a WHERE id_empresa = $empresa AND a.destacado = TRUE AND a.visible = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------PRODUCTOS-----------------------------------------*/
	
	function RecuperarProdShow($empresa){
		$sql="SELECT * FROM producto_show c WHERE c.id_empresa = $empresa;";
		//$sql = "SELECT DISTINCT c.* FROM super_categoria_producto c, producto p, categoria_producto cp WHERE c.id_empresa = #empresa AND c.habilitado = TRUE AND cp.id_categoria = p.id_categoria AND c.id_super_categoria = cp.id_super_categoria GROUP BY c.id_super_categoria;";
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarProductos($empresa){
		$sql="SELECT * FROM producto a, categoria_producto c, marca m WHERE a.id_empresa = $empresa AND m.id_marca = a.id_marca AND c.id_categoria = a.id_categoria AND a.habilitado = TRUE AND c.habilitado = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFotoDefaultProducto($id_producto){
		$sql="SELECT a.* FROM producto_foto a WHERE id_producto = $id_producto AND a.defaulte = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFotoProducto($id_producto){
		$sql="SELECT a.* FROM producto_foto a WHERE id_producto = $id_producto;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarProducto($id_producto){
		$sql="SELECT * FROM producto a, categoria_producto c, marca m WHERE a.id_producto = $id_producto AND m.id_marca = a.id_marca AND c.id_categoria = a.id_categoria AND a.habilitado = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}	

	function RecuperarCategorias($empresa){
		$sql="SELECT DISTINCT c.*, count(*) FROM `categoria_producto` c, producto p WHERE p.id_categoria = c.id_categoria AND c.id_empresa = $empresa AND c.habilitado = TRUE AND c.id_super_categoria IS NULL GROUP BY id_categoria;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarCategoriasTodas($empresa){
		$sql="SELECT DISTINCT c.*, count(*) FROM `categoria_producto` c, producto p WHERE p.id_categoria = c.id_categoria AND c.id_empresa = $empresa AND c.habilitado = TRUE GROUP BY id_categoria;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarCategoriasSuper($empresa, $scat){
		$sql= "SELECT DISTINCT c.*, count(*) FROM `categoria_producto` c, producto p WHERE p.id_categoria = c.id_categoria AND c.id_empresa = $empresa AND c.habilitado = TRUE AND c.id_super_categoria = $scat GROUP BY id_categoria;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarSuperCategorias($empresa){
		$sql="SELECT * FROM super_categoria_producto c WHERE c.id_empresa = $empresa AND c.habilitado = TRUE;";
		//$sql = "SELECT DISTINCT c.* FROM super_categoria_producto c, producto p, categoria_producto cp WHERE c.id_empresa = #empresa AND c.habilitado = TRUE AND cp.id_categoria = p.id_categoria AND c.id_super_categoria = cp.id_super_categoria GROUP BY c.id_super_categoria;";
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarProductosCategoria($empresa, $cat){
		$sql="SELECT * FROM producto a, categoria_producto c, marca m WHERE a.id_empresa = $empresa AND a.id_categoria = $cat AND m.id_marca = a.id_marca AND c.id_categoria = a.id_categoria AND a.habilitado = TRUE AND c.habilitado = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------FOTOGRAFIAS---------------------------------------*/
	function RecuperarGaleriasCount($empresa){
		$sql="SELECT count(*) FROM gallery a WHERE a.id_empresa = $empresa AND a.visible = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}

	function RecuperarFotosTopGallery($id_gallery){
		$sql="SELECT a.* FROM photo_gallery a WHERE a.id_gallery = $id_gallery AND a.visible = TRUE LIMIT 0, 5;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFotosGallery($id_gallery){
		$sql="SELECT a.* FROM photo_gallery a WHERE a.id_gallery = $id_gallery AND a.visible = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarGallery($id_gallery){
		$sql="SELECT a.* FROM gallery a WHERE a.id_gallery = $id_gallery AND a.visible = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------ACERCA DE-----------------------------------------*/

	function RecuperarAboutCount($empresa){
		$sql="SELECT count(*) FROM about a WHERE a.id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}
	
	function RecuperarAbouts($id_empresa){
		$sql="SELECT * FROM about a WHERE a.id_empresa = $id_empresa AND a.defaulte=FALSE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarPhotoAbout($id_about){
		$sql="SELECT * FROM photo_about a WHERE a.id_about = $id_about;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarAboutDefault($id_empresa){
		$sql="SELECT * FROM about a WHERE a.id_empresa = $id_empresa AND a.defaulte = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}

	/*----------------------------------------SERVICIOS-----------------------------------------*/

	function RecuperarServicioCount($empresa){
		$sql="SELECT count(*) FROM servicio a WHERE a.id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}
	
	function RecuperarServicios($id_empresa){
		$sql="SELECT * FROM servicio a WHERE a.id_empresa = $id_empresa AND a.visible=TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarPhotoServicio($id_about){
		$sql="SELECT * FROM servicio_foto a WHERE a.id_servicio = $id_about;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------CLIENTES-----------------------------------------*/
	function RecuperarClientesCount($empresa){
		$sql="SELECT count(*) FROM cliente a WHERE a.id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}
	
	function RecuperarClientes($id_empresa){
		$sql="SELECT * FROM cliente a WHERE a.id_empresa = $id_empresa ORDER BY a.nombre;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------NOTICIAS-----------------------------------------*/
	
	function RecuperarTypePub($id_empresa){
		$sql="SELECT DISTINCT t.* FROM `type_publication` t, publicacion a WHERE a.id_tipo = t.id_type AND a.id_empresa = $id_empresa AND a.visible = TRUE";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarNewsCount($empresa, $tipo_pub){
		$sql="SELECT count(*) FROM publicacion a WHERE a.id_empresa = $empresa AND a.visible = TRUE AND a.id_tipo = $tipo_pub;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}	
	
	function RecuperarNoticias($id_empresa){
		$sql="SELECT * FROM publicacion a WHERE a.id_empresa = $id_empresa AND a.visible = TRUE ORDER BY a.fecha;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarUltimasNoticias($id_empresa, $pub){
		$sql="SELECT * FROM publicacion a WHERE a.id_empresa = $id_empresa AND a.visible = TRUE AND a.id_tipo = $pub ORDER BY a.fecha DESC LIMIT 0, 4;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarNoticia($id_publicacion){
		$sql="SELECT * FROM publicacion a WHERE a.id_publicacion = $id_publicacion;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFotoDefaultNoticia($id_publicacion){
		$sql="SELECT a.* FROM publicacion_foto a WHERE a.id_publicacion = $id_publicacion AND a.defaulte = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarFotoNoticia($id_publicacion){
		$sql="SELECT * FROM publicacion_foto a WHERE a.id_publicacion = $id_publicacion;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	/*----------------------------------------PUBLICIDAD----------------------------------------*/
	function RecuperarPublicidadCount($empresa){
		$sql="SELECT count(*) FROM archivo a WHERE a.id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}
	
	function RecuperarPublicidad($id_empresa, $index){
		$sql="SELECT * FROM archivo a WHERE a.id_empresa = $id_empresa LIMIT $index, 1;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}

	/*----------------------------------------CONTACTO-----------------------------------------*/
	
	function RecuperarContactoCount($empresa){
		$sql="SELECT count(*) FROM contacto a WHERE a.id_empresa = $empresa;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}
	
	function RecuperarContactoDefCount($empresa){
		$sql="SELECT count(*) FROM contacto a WHERE a.id_empresa = $empresa AND a.defaulte = TRUE;";
		
		try{
			$result = $this->db->Execute($sql);
			
			if ($row=mysqli_fetch_array($result, MYSQL_NUM)){
				return $row[0];
			}else
			return 0;
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return 0;
	}	
	
	function RecuperarContactos($id_empresa){
		$sql="SELECT * FROM contacto a WHERE a.id_empresa = $id_empresa ORDER BY a.defaulte desc;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	function RecuperarContactoDef($id_empresa){
		$sql="SELECT * FROM contacto a WHERE a.id_empresa = $id_empresa AND a.defaulte = TRUE ORDER BY a.defaulte desc;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
	
	function RecuperarCategoria($categoria){
		$sql="SELECT c.Nombre FROM Cat_Categoria c WHERE id = $categoria limit 1;";
		
		try{
			$result = $this->db->Execute($sql);
		}
		catch(Exception $ex){
			// si existe un error se deshace la transacci&#65533;n
			throw new Exception($ex->getMessage());
		}

		return $result;
	}
	
}
?>